package com.zhongcw.test.spring.boot.jpa.repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.zhongcw.test.spring.boot.jpa.po.Commodity;

public interface CommodityRepository extends CrudRepository<Commodity, String> {
    Commodity findByCommodityCode(String commodityCode);

    List<Commodity> findByCommodityName(String commodityName);

    //使用原始SQL
//	 @Query(value="UPDATE commodity SET last_update_time = NOW() WHERE commodity_code= ? ",nativeQuery=true)
    @Modifying
    @Transactional
    // HQL
    @Query("UPDATE Commodity SET lastUpdateTime = NOW() where commodityCode= :commodityCode")
    int updateCommodityNameByCommodityCode(@Param("commodityCode") String commodityCode);

    @Modifying
    @Transactional
    @Query("UPDATE Commodity SET lastUpdateTime = NOW() where commodityCode= :#{#commodity.commodityCode}")
    Integer updateByCommodityCode(@Param("commodity") Commodity commodity);

    /**
     * 使用 IN 子句
     *
     * @param IN 数组
     * @author: ZCW
     * @createTime: 2017/12/25
     * @lastUpdateTime: 2017/12/25 ZCW
     * @return:
     */
    List<Commodity> findByCommodityCodeIn(String... param);

    @Query(value = "select sum(c.commodityPrice) from Commodity c where c.createTime < :create_time")
    Object findSumCommodityPriceByCreateTime(@Param("create_time") Date create_time);

    List<Commodity> findByCreateTimeLessThan(Date create_time);

    // code name price CT
    List<Commodity> findByCommodityCodeInAndCommodityNameLikeAndCommodityPriceBetweenAndCreateTimeBetweenOrderByCommodityPriceDescCreateTimeDesc(
            List<String> commodityCode,
            String CommodityName,
            BigDecimal min,
            BigDecimal max,
            Date startDate,
            Date endDate
    );

    @Modifying
    @Transactional
//    @Query("update Commodity c set c.lastUpdateTime = :lastUpdateTime where c.id = :id") // jpql
    @Query(value = "update commodity c set c.last_update_time = :lastUpdateTime where c.id = :id",nativeQuery = true) // nativ sql
    int updateLastUpdateTimeById(@Param("id") String id, @Param("lastUpdateTime") Date lastUpdateTime);

    /* 分页 */
    Page<Commodity> findAll(Pageable pageable);

}
